package export_excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.agile4j.plugin.gun.helper.GunHelper;
import org.agile4j.plugin.gun.model.FieldEntity;
import org.agile4j.plugin.gun.utils.ExcelUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel 帮助类
 * 
 * @author hanyx
 * @since
 */
public final class ExcelHelper {
	private static final Logger LOGGER = LogManager.getLogger(ExcelHelper.class);

	/**
	 * 读取指定路径下Excel文件， 并以FieldEntity 集合形式返回
	 */
	public static List<FieldEntity> loadFieldData() {
		return loadFieldData(GunHelper.getFieldReplaceDataExcelFileUrl()) ;
	}
	
	/**
	 * 读取指定路径下Excel文件， 并以FieldEntity 集合形式返回
	 */
	public static List<FieldEntity> loadFieldData(String excelPath) {
		List<FieldEntity> pojoList = new ArrayList<>();

		// 设置要读取的文件路径
		try (InputStream dataXls = new FileInputStream(excelPath)) {
			XSSFWorkbook wb = new XSSFWorkbook(dataXls);
			XSSFSheet sheet1 = wb.getSheetAt(0);
			XSSFRow row;
			for (int i = sheet1.getFirstRowNum(); i < sheet1.getPhysicalNumberOfRows(); i++) {
				if (i > 0) {
					row = sheet1.getRow(i);
					FieldEntity pojo = new FieldEntity();
					pojo.setFieldType(sheet1.getSheetName());
					for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
						// 通过 row.getCell(j).toString() 获取单元格内容，
						if (j == 0) {
							pojo.setFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
						} else if (j == 1) {
							pojo.setFieldCnName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojo.setNewFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojoList.add(pojo);
						} 
					}
				}
			}
			
			XSSFSheet sheet2 = wb.getSheetAt(1);
			for (int i = sheet2.getFirstRowNum(); i < sheet2.getPhysicalNumberOfRows(); i++) {
				if (i > 0) {
					row = sheet2.getRow(i);
					FieldEntity pojo = new FieldEntity();
					pojo.setFieldType(sheet2.getSheetName());
					for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
						// 通过 row.getCell(j).toString() 获取单元格内容，
						if (j == 0) {
							pojo.setFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
						} else if (j == 1) {
							pojo.setFieldCnName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojo.setNewFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojoList.add(pojo);
						} 
					}
				}
			}
			
			XSSFSheet sheet3 = wb.getSheetAt(2);
			for (int i = sheet3.getFirstRowNum(); i < sheet3.getPhysicalNumberOfRows(); i++) {
				if (i > 0) {
					row = sheet3.getRow(i);
					FieldEntity pojo = new FieldEntity();
					pojo.setFieldType(sheet3.getSheetName());
					for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
						// 通过 row.getCell(j).toString() 获取单元格内容，
						if (j == 0) {
							pojo.setFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
						} else if (j == 1) {
							pojo.setFieldCnName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojo.setNewFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojoList.add(pojo);
						} 
					}
				}
			}
			
			XSSFSheet sheet4 = wb.getSheet("dict");
			for (int i = sheet4.getFirstRowNum(); i < sheet4.getPhysicalNumberOfRows(); i++) {
				if (i > 0) {
					row = sheet4.getRow(i);
					FieldEntity pojo = new FieldEntity();
					pojo.setFieldType(sheet4.getSheetName());
					for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
						// 通过 row.getCell(j).toString() 获取单元格内容，
						if (j == 0) {
							pojo.setFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
						} else if (j == 1) {
							pojo.setFieldCnName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojo.setNewFieldName(row.getCell(j) != null ? row.getCell(j).toString() : null);
							pojoList.add(pojo);
						} 
					}
				}
			}
			
//			LOGGER.debug(pojoList);
		} catch (Exception e) {
			LOGGER.error("save Data failur", e);
 			throw new RuntimeException(e);
		}
		return pojoList;
	}
	
	/**
	 * 把FieldEntity对象数据保存到 Excel文件中， 指定路径
	 */
	public static void saveReplaceData(List<FieldEntity> pojoList, String excelPath) {
		if (CollectionUtils.isNotEmpty(pojoList)) {
			LinkedHashMap<String, String> propertyHeaderMap = new LinkedHashMap<>();
			propertyHeaderMap.put("fieldType", "field_type");
			propertyHeaderMap.put("fieldName", "field_name");
			propertyHeaderMap.put("fieldCnName", "field_cn_name");
			propertyHeaderMap.put("newFieldName", "new_field_name");

			try (OutputStream out = new FileOutputStream(excelPath)) {
				XSSFWorkbook ex = ExcelUtils.generateXlsxWorkbook("filed_data", propertyHeaderMap, pojoList);
				ex.write(out);
				LOGGER.debug("导出成功！");
			} catch (Exception e) {
				LOGGER.error("save Data failur", e);
				throw new RuntimeException(e);
			}
		}
	}
	
	public static void main(String[] args) {
		List<FieldEntity> list = loadFieldData() ;
		saveReplaceData(list, "E:\\new_replace_data1111.xlsx") ;
	}
}
